﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace XrmLibrary.Extensions
{
    public static class SqlConnectionExtensions
    {
        /// <summary>
        /// Performs a SQL SELECT statement and retrieves results in a SqlDataReader.
        /// </summary>
        /// <typeparam name="T">The type of data returned from the operation.</typeparam>
        /// <param name="sqlConnection">A SqlConnection to a SQL Server database.</param>
        /// <param name="query">A SQL SELECT statement. Rows are returned via a SqlDataReader.</param>
        /// <param name="operation">A function to perform on the SqlDataReader object as it is enumerated.</param>
        /// <param name="parameters">Optional SqlParameters to be attached to the SqlCommand.</param>
        /// <returns>The results of the query in the form returned from the operation.</returns>
        public static IEnumerable<T> SqlRetrieveMultiple<T>(this SqlConnection sqlConnection, String query, Func<SqlDataReader, T> operation, params SqlParameter[] parameters)
        {
            sqlConnection.Open();
            using (var command = new SqlCommand(query, sqlConnection))
            {
                if (parameters.Length > 0)
                {
                    command.Parameters.AddRange(parameters);
                }
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        yield return operation(reader);
                    }
                }
            }
        }

        public static T SqlExecuteScalar<T>(this SqlConnection sqlConnection, String query, params SqlParameter[] parameters)
        {
            sqlConnection.Open();
            using (var command = new SqlCommand(query, sqlConnection))
            {
                if (parameters.Length > 0)
                {
                    command.Parameters.AddRange(parameters);
                }
                return (T)command.ExecuteScalar();
            }
        }

        public static Int32 SqlExecuteNonQuery(this SqlConnection sqlConnection, String query, params SqlParameter[] parameters)
        {
            sqlConnection.Open();
            using (var command = new SqlCommand(query, sqlConnection))
            {
                if (parameters.Length > 0)
                {
                    command.Parameters.AddRange(parameters);
                }
                return command.ExecuteNonQuery();
            }
        }
    }
}
